<?php
defined( '_JEXEC' ) or die( 'Restricted access' );
jimport('joomla.application.component.model');

class linioModellinio extends JModel
{
	const PRICE_LOW      = 1;
	const PRICE_HIGH     = 2;
	const PRICE_ESPECIAL = 3;
	const FREE_SHIPPING  = 4;
	const DESCRIPTION	 = 5;
	
	private $mensaje;
	public  $stdParams;
	public  $LINIO_MENU;
	public  $LINIO_BRANDS;
	public  $LINIO_CSV;
	
	function __construct(){
		parent::__construct();
		$this->mensaje = array();
		$this->LINIO_MENU = $this->_db->nameQuote('#__liniomenu');
		$this->LINIO_BRANDS = $this->_db->nameQuote('#__liniobrands');
		$this->LINIO_CSV = $this->_db->nameQuote('#__liniocsv');
	}
		
	/**
	 * 
	 * Recuperar productos para el home.
	 */
	function home($limit=0, $offset=0, $brand=null, $sort=null) {
		$retval = new stdClass();
		$c = $this->_db->nameQuote('category');
		$b = $this->_db->nameQuote('brand');
		
		$sql = "SELECT %s FROM ".$this->_db->nameQuote('#__liniocsv');
		
		if($brand == null){
			$sql.=" WHERE category IN (SELECT category FROM ".
					$this->_db->nameQuote('#__liniomenu').") GROUP BY (%s)";
				   
			//obtener las marcas
			$query = sprintf($sql, $b.", COUNT(*) AS total",$c)." ORDER BY total DESC LIMIT 20";
			$this->log("[home] QUERY total brands=".$query);
			$this->_db->setQuery($query);		
			$retval->stdBrands = $this->_db->loadObjectList();		
			   
		} else {
			$sql.=" WHERE ".$b."=".$this->_db->quote($brand);			
			// total de productos
			$query = sprintf($sql,"COUNT(*) AS total",$c);
			$this->log("[home] QUERY brand=".$query); 
			$this->_db->setQuery($query);
			$retval->total = (int) $this->_db->loadResult();

			// obtener categorias.
			$queryC = sprintf($sql,"category, COUNT(*) AS total"); 
			$queryC.= " GROUP BY ".$this->_db->nameQuote(category);			
			$query = "SELECT M.parentId,M.id,M.description,C.category,C.total FROM ".
			 		 $this->_db->nameQuote('#__liniomenu').
			 		 " AS M JOIN (".$queryC.") AS C ON M.category = C.category";
			
			$this->log("[home] QUERY home=".$query);
			$this->_db->setQuery($query);
			$retval->stdCategories =  $this->_db->loadObjectList();
		}			   
			   
		//obtener los productos	
		$query = sprintf($sql,"SQL_CALC_FOUND_ROWS *",$c);

		// poner limit y offset
		$query.= $this->getSortQuery($sort);
		$query.= $this->getLimitOffsetQuery($limit,$offset);		
		$this->log("[home] QUERY=".$query);				
		$this->_db->setQuery($query);
		$retval->stdItemsArray = $this->_db->loadObjectList();

		$totalRows = $this->getTotalRows("SELECT count(*) as counter FROM(".sprintf($sql,$c,$c).") AS A");
		$retval->total = $totalRows;  
		$this->log("[home] totalRows = ".$totalRows);

		return $retval;
	}
	
	/**
	 * 
	 * Buscar un articulo por descripcion de producto.
	 * @param $cadena
	 * @param $limit
	 * @param $offset
	 * @param $category
	 * @param $orderBy
	 * @param $orderType
	 */
	function search($cadena, $limit=0, $offset=0, $category=null, $brand=null,$sort=null,$filer=null){
		$retval = new stdClass();
		
		$sql = "SELECT %s FROM ".
			   $this->_db->nameQuote('#__liniocsv').
			   " WHERE description LIKE ".
			   $this->_db->quote('%%'.$cadena.'%%'); //el doble %% es para "escapar" el % de sprintf
			   
		if($category != null){
			$sql.=" AND category = ".$this->_db->quote($category)." ";			
		}
		
		if($brand != null){
			$sql.=" AND brand = ".$this->_db->quote($brand)." ";			
		}		
		
		if($filer != null){
			if($filer == self::PRICE_ESPECIAL){
				$sql.=" AND  priceSpecial <> ''";
			}
			if($filer == self::FREE_SHIPPING){
				$sql.=" AND  shippingFree = ".$this->_db->quote('true');
			}
		}
		
		// obtener los resultados
		$query = sprintf($sql,"SQL_CALC_FOUND_ROWS *");
		
		// agregar filtros y ordenamiento.
		$query.= $this->getSortQuery($sort);			 
		$query.= $this->getLimitOffsetQuery($limit,$offset);
			
		$this->_db->setQuery($query);
		$retval->stdItemsArray = $this->_db->loadObjectList();
		
		// obtener el total de articulos.
		$retval->total = $this->getTotalRows(sprintf($sql,"COUNT(*) AS TOTAL"));		
		
		// cargar las categorias...
		$retval->stdCategories = $this->getSearchCategories($cadena);	

		//cargar marcas...	
		$retval->stdBrands = $this->getBrands(null,$cadena);
		
		//filtros
		$retval->astdFilters = $this->getFilters();
		$this->log("[search] retval = ".print_r($retval,true));
		return $retval;
	}
	
	/**
	 * 
	 * Metodo para buscar Items o articulos a partir de un menu padre
	 * @param int $menuId.- Id del menu
	 * @param int $limit.- Limite maximo de articulos a recuperar.
	 * @param int $offset.- Pagina a recuperar.
	 * @param string $orderBy.- Ordenado por (price, description, etc)
	 * @param String $orderType: Puede ser Ascendente o Descendente
	 */
	function getItems($menuId, $limit=0, $offset=0, $brand=null,$sort=null,$filer=null){
		
		$retval = new stdClass();
		$sql = "SELECT %s FROM ".
		$this->_db->nameQuote('#__liniocsv')." AS L JOIN ".
		$this->_db->nameQuote('#__liniomenu').
		" AS M ON L.category LIKE CONCAT(M.category,'%%') WHERE M.id = ".$menuId; //el doble %% es para "escapar" el % de sprintf
		
		if($brand != null){
			$sql.=" AND L.brand = ".$this->_db->quote($brand)." ";			
		}		
		
		if($filer != null){
			if($filer == self::PRICE_ESPECIAL){
				$sql.=" AND  L.priceSpecial <> ''";
			}
			if($filer == self::FREE_SHIPPING){
				$sql.=" AND  L.shippingFree = ".$this->_db->quote('true');
			}
		}		
		
		//obtener el total de registros...		
		$query = sprintf($sql,"COUNT(*) AS TOTAL"); 
		$this->_db->setQuery($query);
		$retval->total = (int) $this->_db->loadResult();
		
		// obtener los resultados
		$query = sprintf($sql,"SQL_CALC_FOUND_ROWS *");
		
		// agregar filtros y ordenamiento.
		$query.= $this->getSortQuery($sort,"L.");	
		$query.= $this->getLimitOffsetQuery($limit,$offset);			 

		$this->log("[getItems] QUERY=".$query);	
		$this->_db->setQuery($query);
		$retval->stdItemsArray = $this->_db->loadObjectList();
		
		// cargar las categorias...				
		$retval->stdCategories = $this->getGroupsId("category",$menuId);
		
		//cargar marcas...	
		$retval->stdBrands = $this->getBrands($menuId, null);
		
		//filtros
		$retval->astdFilters = $this->getFilters();		
		
		//path_from_root
		$retval->path_from_root = $this->getPathFromRoot($menuId);
		$this->log("[getItems] retval = ".print_r($retval,true));
		return $retval;
	}	
	
	/**
	 * 
	 * Recuperar las marcas de
	 * @param $category
	 * @param $description
	 */
	function getBrands($category,$description){
		$where = "1";
		$sql = "SELECT brand,COUNT(brand) as total FROM ".
			   $this->_db->nameQuote('#__liniocsv').
			   " WHERE %s".
		   	   " GROUP BY brand".
		   	   " ORDER BY total DESC".
			   " LIMIT 20";
		if($description != null){
			$where = "description LIKE ".$this->_db->quote('%%'.$description.'%%');
		} 
		if($category != null){
			$where = "category IN (".
					 "SELECT category FROM ".$this->_db->nameQuote('#__liniomenu')." WHERE parentId=".$category.
					 " OR Id=".$category.
					 ")";			
		}
		$query = sprintf($sql,$where);
		$this->log("[getBrands] query = ".$query);
		$this->_db->setQuery($query);
		return $this->_db->loadObjectList();		
	}
	
	/**
	 * Metodo para buscar sub categorias o marcas de una cadena de busqueda dada
	 *
	 */
	function getGroups($field,$cadena,$category=null){
		$query = "SELECT ".$field.", COUNT(".$field.") AS total".
			     " FROM ".$this->_db->nameQuote('#__liniocsv').
			     " WHERE description LIKE ".$this->_db->quote('%%'.$cadena.'%%');		
		if($category != null){
			$query.=" AND category = ".$this->_db->quote($category)." ";			
		}		
		$query.=" GROUP BY ".$field."";	    
		$this->_db->setQuery($query);
		return $this->_db->loadObjectList();	
	}
	
	/**
	 * 
	 * Metodo para buscar sub categorias o marcas de un ID de menu.
	 * @param $field.- Campo de busqueda
	 * @param $id.- clave del Item de menu.
	 */
	function getGroupsId($field,$id){

		$query = "SELECT ".$field.", COUNT(".$field.") AS total".
				 " FROM ".$this->_db->nameQuote('#__liniocsv').
				 " WHERE category IN (SELECT category from ".
				  $this->_db->nameQuote('#__liniomenu')." WHERE parentId=".$id.")".
				 " GROUP BY ".$field;			
		
		if($field == 'category'){
			$query = "SELECT M.parentId,M.id,M.description,C.category,C.total".
			 		 " FROM ".$this->_db->nameQuote('#__liniomenu')."AS M JOIN ( ".
	    			 $query.
	    			 " ) AS C ON M.category = C.category".
	    			 " WHERE M.parentId =".$id;
	    } 		
		$this->_db->setQuery($query);
		return $this->_db->loadObjectList();	
	}	

	/**
	 * 
	 * Obtener las categorias de una busqueda dada, con el total de elementos
	 * y las claves de la categoria.
	 * @param String $cadena
	 */
	function getSearchCategories($cadena,$id=null){
		$query = "SELECT M.parentId,M.id,M.description,C.category,C.total".
				 " FROM ".$this->_db->nameQuote('#__liniomenu')."AS M JOIN ( ".
				 " SELECT category, COUNT(category) AS total".
			     " FROM ".$this->_db->nameQuote('#__liniocsv').
			     " WHERE description LIKE ".$this->_db->quote('%%'.$cadena.'%%').
			     " GROUP BY category".
				 " ) AS C ON M.category = C.category";
		if($id != null){
			$query.= " WHERE M.parentId =".$id;
		}
		$this->_db->setQuery($query);
		return $this->_db->loadObjectList();		
	}
	

	
	/**
	 *
	 * consulta los datos de los parametros
	 */
	function getParametros(){
		$this->stdParams = new JObject();
		$this->_db->setQuery("SELECT * FROM #__linioparams WHERE activo = 1");
		$parametros = $this->_db->loadObjectList();	
		foreach ($parametros as $value) {
			$this->stdParams->set($value->campo,$value->valor);			
		}					
	}
	
	/**
	 *
	 * consulta el o los paises de linio.
	 * @param string $country.- Clave del pais o nulo para todos.
	 */
	function getCountry($country=null){
	    $sql = "SELECT * FROM #__liniocountry";
		if($country != null){
		   $sql.= " WHERE country = ".$this->_db->quote($country);
		} else {
		   $sql.= " WHERE active = 1";
		}
		$this->_db->setQuery($sql);
		$countries = $this->_db->loadObjectList();	
		if($country == null){
		   return $countries;
		} else {
			foreach ($countries as $c) {
				if($c->country == $country){
				   return $c;
				}
			}				
		} 
		return null;
	}			
	
	/**
	 *
	 * regresa un parametro de la BD.
	 * @param string $param.- Clave del parametro a recuperar.
	 */
	function getParam($param,$default=null){
		if($this->stdParams == null){
			$this->getParametros();
		} 		
		return $this->stdParams->get($param,$default);		
	}
	
	/**
	 * 
	 * Metodo para recuperar los filtros de busqueda
	 */
	private function getFilters(){
		$retval = array();
		$retval[] = $this->createFilter(self::PRICE_ESPECIAL,JText::_('PRICE_ESPECIAL'));	
		$retval[] = $this->createFilter(self::FREE_SHIPPING,JText::_('FREE_SHIPPING'));	
		return $retval;
	}
	
	/**
	 * 
	 * Metodo para obtener los tipo de ordenamiento
	 */
	private function getSorts(){
		$retval = array();
		$retval[] = $this->createFilter(self::PRICE_LOW,JText::_('PRICE_LOW'));	
		$retval[] = $this->createFilter(self::PRICE_HIGH,JText::_('PRICE_HIGH'));
		$retval[] = $this->createFilter(self::DESCRIPTION,JText::_('DESCRIPTION'));		
		return $retval;
	}	
	
	/**
	 * 
	 * Obtener el combobox de ordenamiento
	 * @param $selected
	 */
	function getSortsBox($selected=self::PRICE_LOW)
	{
		// Initialize variables
		$aSort = array ();
		$aSort[] = JHTML::_('select.option', self::PRICE_LOW,  JText::_('PRICE_LOW'));
		$aSort[] = JHTML::_('select.option', self::PRICE_HIGH, JText::_('PRICE_HIGH'));
		$aSort[] = JHTML::_('select.option', self::DESCRIPTION,JText::_('DESCRIPTION'));
		// Build the select list
		$html = JHTML::_('select.genericlist',  $aSort, 'sort', 'class="inputbox" size="1" onchange="document.forms[\'adminForm\'].submit();"', 'value', 'text', $selected);
		return $html;
	}	
	
	/**
	 * 
	 * Crear un objeto de tipo stdClass con un tipo de filtro.
	 * @param int $id
	 * @param string $description
	 */
	private function createFilter($id,$description){
		$retval  = new stdClass();
		$retval->id = $id;
		$retval->description = $description;
		return $retval;
	}	
	
	/**
	 * 
	 * Obtener la sentencia SQL para un limit y offset
	 * @param int $limit
	 * @param int $offset
	 * @param int $total
	 */
	private function getLimitOffsetQuery($limit=0,$offset=0){
		$sql = '';
		if($limit > 0 ){
			$sql.=" LIMIT  ".$limit;
		}
		if($offset > 0){
			$sql.=" OFFSET ".$offset;
		}	
		return $sql;	
	}
	
	/**
	 * 
	 * Obtiene el query para el ordenamiento
	 * @param int $sort
	 */
	private function getSortQuery($sort,$alias=''){
		$query = "";
		if($sort != null){
			$query.=" ORDER BY ";
			if($sort == self::PRICE_LOW){
				$query.=" `price` ASC";
			}
			if($sort == self::PRICE_HIGH){
				$query.=" `price` DESC";
			}	
			if($sort == self::DESCRIPTION){
				$query.= $alias."`description`";
			}
		}	
		return $query;		
	}
	
	/**
	 * 
	 * Recuperar el path de navegacion
	 * @param int $menuId
	 */
	private function getPathFromRoot($menuId=0){
		$retval = array();
		$clazz = new stdClass();

		while($clazz != null){
			$this->_db->setQuery("SELECT * FROM #__liniomenu WHERE id=".$menuId);
			$clazz = $this->_db->loadObject();
			if($clazz != null){
				$retval[] = $clazz;
				$menuId = $clazz->parentid;
			}			
		}
		return array_reverse($retval);
	}
	
	/**
	 * 
	 * Regresa los mensajes del proceso como HTML
	 */
	function getMensajeAsHTML(){
		$retval = '';
		foreach ($this->mensaje as $value) {
			$retval.= $value.'<br/>';
		}
		return $retval;
	}
	
	/**
	 * 
	 * Regresa el arreglo con los mensajes del sistema.
	 */
	function getMensaje(){
		return $this->mensaje;
	}
	
	/**
	 * 
	 * Regresa un arreglo stdClass Object del menu
	 * @param int $parentId.- Id del padre a recuperar.
	 */
	function getMenu($country=null,$parentId=-1){
		$query = "SELECT * FROM ".$this->_db->nameQuote('#__liniomenu');
		if($parentId != -1){
			$query.=" WHERE parentid = ".$parentId;	
		}		
		$this->_db->setQuery($query);
		return $this->_db->loadObjectList();				
	}	
	
	/**
	 * 
	 * Recuperar el total de registros de un Query Previo.
	 * @param $query
	 */
	private function getTotalRows($query=null){		
		$this->_db->setQuery("SELECT FOUND_ROWS()");
		$retval = (int) $this->_db->loadResult();
		if(($retval == 0 || $retval == null) && $query != null){
			$this->log("[getTotalRows] QUERY =".$query);
			$this->_db->setQuery($query);
			$retval = (int) $this->_db->loadResult();
		}	
		return $retval;
	}
		
	/**
	 * 
	 * Recuperar la URL de un articulo.
	 * @param $itemId
	 */
	function getArticle($itemId){
		$query="SELECT `productLink` FROM ".$this->_db->nameQuote('#__liniocsv')." WHERE `productId` = ".$itemId;
		$this->_db->setQuery($query);
		$retval = $this->_db->loadResult();
		return $retval;
	}
	
	function buscar($where){
		$query="SELECT L.*,B.id as brandId,TRUNCATE(L.`price`/(1-(L.`priceSpecial`/100)),2) AS OrigianlPrice, M.id AS menuId, M.description as menuDescription ".
				"FROM `#__liniocsv` AS L LEFT JOIN `#__liniobrands` AS B ". 
				"ON L.`brand` = B.`brand` ".
				"LEFT JOIN `#__liniomenu` AS M ". 
				"ON L.`category` = M.`category` ".
				"WHERE ".$where;		
		$this->_db->setQuery($query);
		return $this->_db->loadObjectList();
	}
		
	
	/**
	 * 
	 * Escribir un archivo LOG
	 * @param unknown_type $mensaje
	 */
	private function log($mensaje){
//		$fp = fopen('C:\\linio.log', 'a');
//		fwrite($fp, date("Y-m-d H:i:s").'[linio.php]'.$mensaje.PHP_EOL);
//		fclose($fp);
	}
}
?>